﻿using nsBaseClass;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using OpelWelcomeScreenWeb.Models;
using System.Collections;
using log4net;

namespace OpelWelcomeScreenWeb.Database
{
    public class DBAccess
    {
        protected static readonly ILog logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        
        public static Hashtable loadWorkOrders(string site, int overdue, int aftertime)
        {
            var mapResult = new Hashtable();
            var globalVars = new clsGlobalVariable();
            var hSql = new clsSqlFactory();
            var objUtil = new clsBaseUtility();
            try
            {
                // string sql = "SELECT ADVISORNAME,CHECKINTIME,CUSTFNAME,CUSTLNAME FROM dbo.fn_BmwWelcomeScreen('{0}',{1},{2})";
                string sql = "SELECT ADVISORNAME,ADVISORPICREF,ADVISORID,CHECKINTIME,CUSTFNAME,CUSTLNAME,WORKORDERID,WORKORDERSTATUS,LICENSENUMBER FROM dbo.fn_BmwWelcomeScreen('{0}',{1},{2})";
                sql = String.Format(sql, site, overdue, aftertime);

                hSql.NewCommand(sql);
                hSql.ExecuteReader();
                while (hSql.Read())
                {
                    var reader = hSql.Reader;
                    var workOrder = new WorkOrder();
                    var advisorName = reader.GetString(reader.GetOrdinal("ADVISORNAME"));
                    var advisorPicRef = reader.GetString(reader.GetOrdinal("ADVISORPICREF"));
                    var checkIntime = DateTime.Now.Date 
                        + hSql.Reader.GetTime(reader.GetOrdinal("CHECKINTIME"));
                    var customerName = hSql.Reader.GetString(reader.GetOrdinal("CUSTFNAME")).Trim() 
                        + " "
                        + hSql.Reader.GetString(reader.GetOrdinal("CUSTLNAME")).Trim();
                    var workOrderStatus = reader.GetString(reader.GetOrdinal("WORKORDERSTATUS"));
                    var licenseNumber = reader.GetString(reader.GetOrdinal("LICENSENUMBER"));
                    workOrder.AdvisorName = advisorName;
                    workOrder.AdvisorPicRef = advisorPicRef;
                    workOrder.CheckInTime = checkIntime;
                    workOrder.CustomerName = customerName;
                    workOrder.LicenseNo = licenseNumber;
                    workOrder.Status = workOrderStatus;


                    logger.Debug("============= WorkOrder: " + workOrder);

                    if (mapResult[advisorName] != null)
                    {
                        var workOrders = mapResult[advisorName] as ArrayList;
                        workOrders.Add(workOrder);
                    }
                    else
                    {
                        var workOrders = new ArrayList();
                        workOrders.Add(workOrder);
                        mapResult[advisorName] = workOrders;
                    }
                }

                /* // test
                for (int i = 0; i < 10; i++)
                {
                    var workOrder = new WorkOrder();
                    workOrder.AdvisorName = "Advisor Name #" + i;
                    workOrder.AdvisorPicRef = "";// "http://placehold.it/123/12" + i;
                    workOrder.CheckInTime = DateTime.Now.AddHours(i);
                    workOrder.CustomerName = "Cust Name #" + i;
                    workOrder.LicenseNo = "ABC-0" + i;
                    workOrder.Status = "A";
                    if (mapResult[workOrder.AdvisorName] != null)
                    {
                        var workOrders = mapResult[workOrder.AdvisorName] as ArrayList;
                        workOrders.Add(workOrder);
                    }
                    else
                    {
                        var workOrders = new ArrayList();
                        workOrders.Add(workOrder);
                        mapResult[workOrder.AdvisorName] = workOrders;
                    }
                }
                // */
            }
            finally
            {
                hSql.Close();
            }
            return mapResult;
        }

        public static Hashtable loadServiceAdvisorPictures(ICollection listAdvisorName)
        {
            var mapResult = new Hashtable();
            var listAdvisor = string.Empty;
            foreach (var advisorName in listAdvisorName)
            {
                listAdvisor += "," + advisorName;
            }
            if (!string.IsNullOrEmpty(listAdvisor))
            {
                var hSql = new clsSqlFactory();
                var objUtil = new clsBaseUtility();
                var query = string.Format("select SMANID, NAME, PICREF from {0} where NAME in (?)", objUtil.getTable("SMAN"));
                try
                {
                    hSql.NewCommand(query);
                    hSql.Com.Parameters.AddWithValue("NAME", listAdvisor.Substring(1));
                    hSql.ExecuteReader();
                    while (hSql.Read())
                    {
                        var adName = hSql.Reader.GetString(1);
                        var picRef = hSql.Reader.GetString(2);
                        mapResult[adName] = picRef;
                    }

                }
                finally
                {
                    hSql.Close();
                }
            }
            return mapResult;
        }

    }
}